package constant.milk.toilet.database;

import java.util.ArrayList;

import constant.milk.toilet.bean.StickerData;
import constant.milk.toilet.bean.TimerData;
import android.content.Context;
import android.database.Cursor;

public class DbSelectQuery
{
	private Context context;
	private DbAdapter db;
	
	public DbSelectQuery(Context context, DbAdapter db)
	{
		this.context = context;
		this.db = db;
	}
	
	public void setBeginTansaction() throws Exception
	{
		db.beginTansaction(context);
	}
	
	public void setSuccessful() throws Exception
	{
		db.setTransactionSuccessful();
	}
	
	public void setEndTansaction() throws Exception
	{
		db.endTransaction(context);
	}
	
	public ArrayList<TimerData> getTimerList(int year, int month, int day)
	{
		ArrayList<TimerData> list = new ArrayList<TimerData>();
		
		String query = "";
		
		query = "SELECT * FROM " + TableName.TIMER_LIST + " WHERE " + 
				"year = " + year + 
				" AND month = " + month + 
				" AND day = " + day + 
				" ORDER BY startTime DESC;";
		
		Cursor c = db.select(context, query);
		
		int idIdx = c.getColumnIndex("id");
		int yearIdx = c.getColumnIndex("year");
		int monthIdx = c.getColumnIndex("month");
		int dayIdx = c.getColumnIndex("day");
		int starttimeIdx = c.getColumnIndex("starttime");
		int termIdx = c.getColumnIndex("term");
		
		c.moveToFirst();
		
		for (int i = 0; i < c.getCount(); i++)
		{
			TimerData data = new TimerData();
			data.setId(c.getInt(idIdx));
			data.setYear(c.getInt(yearIdx));
			data.setMonth(c.getInt(monthIdx));
			data.setDay(c.getInt(dayIdx));
			data.setStarttime(c.getString(starttimeIdx));
			data.setTerm(c.getString(termIdx));
			data.setCheckbox(0);
			
			list.add(data);
			
			c.moveToNext();
		}
		
		if(c != null) c.close();
		
		return list;
	}
	
	public TimerData getTimerId(int id)
	{
		TimerData data = new TimerData();
		
		String query = "";
		
		query = "SELECT * FROM " + TableName.TIMER_LIST + " WHERE id = " + id + ";";
		
		Cursor c = db.select(context, query);
		
		int idIdx = c.getColumnIndex("id");
		int yearIdx = c.getColumnIndex("year");
		int monthIdx = c.getColumnIndex("month");
		int dayIdx = c.getColumnIndex("day");
		int starttimeIdx = c.getColumnIndex("starttime");
		int termIdx = c.getColumnIndex("term");
		
		c.moveToFirst();
		
		for (int i = 0; i < c.getCount(); i++)
		{
			data.setId(c.getInt(idIdx));
			data.setYear(c.getInt(yearIdx));
			data.setMonth(c.getInt(monthIdx));
			data.setDay(c.getInt(dayIdx));
			data.setStarttime(c.getString(starttimeIdx));
			data.setTerm(c.getString(termIdx));
			data.setCheckbox(0);
			
			c.moveToNext();
		}
		
		if(c != null) c.close();
		
		return data;
	}
	
	public ArrayList<TimerData> getTimerStartTime(long startTime)
	{
		ArrayList<TimerData> list = new ArrayList<TimerData>();
		
		String query = "";
		
		query = "SELECT * FROM " + TableName.TIMER_LIST + " WHERE starttime > '" + startTime + "';";
		
		Cursor c = db.select(context, query);
		
		int idIdx = c.getColumnIndex("id");
		int yearIdx = c.getColumnIndex("year");
		int monthIdx = c.getColumnIndex("month");
		int dayIdx = c.getColumnIndex("day");
		int starttimeIdx = c.getColumnIndex("starttime");
		int termIdx = c.getColumnIndex("term");
		
		c.moveToFirst();
		
		for (int i = 0; i < c.getCount(); i++)
		{
			TimerData data = new TimerData();
			data.setId(c.getInt(idIdx));
			data.setYear(c.getInt(yearIdx));
			data.setMonth(c.getInt(monthIdx));
			data.setDay(c.getInt(dayIdx));
			data.setStarttime(c.getString(starttimeIdx));
			data.setTerm(c.getString(termIdx));
			data.setCheckbox(0);
			
			list.add(data);
			
			c.moveToNext();
		}
		
		if(c != null) c.close();
		
		return list;
	}
	
	public StickerData getStickerDate(int year, int month, int day)
	{
		StickerData data = null;
		
		String query = "";
		
		query = "SELECT * FROM " + TableName.STICKER_LIST + " WHERE " + 
				"year = " + year + 
				" AND month = " + month + 
				" AND day = " + day + ";";
		
		Cursor c = db.select(context, query);
		
		int idIdx = c.getColumnIndex("id");
		int yearIdx = c.getColumnIndex("year");
		int monthIdx = c.getColumnIndex("month");
		int dayIdx = c.getColumnIndex("day");
		int stickerIdx = c.getColumnIndex("sticker");
		
		c.moveToFirst();
		
		for (int i = 0; i < c.getCount(); i++)
		{
			data = new StickerData();
			data.setId(c.getInt(idIdx));
			data.setYear(c.getInt(yearIdx));
			data.setMonth(c.getInt(monthIdx));
			data.setDay(c.getInt(dayIdx));
			data.setSticker(c.getInt(stickerIdx) + "");
			
			c.moveToNext();
		}
		
		if(c != null) c.close();
		
		return data;
	}
	
	public ArrayList<TimerData> getTimerYearCount(int year)
	{
		ArrayList<TimerData> list = new ArrayList<TimerData>();
		
		String query = "";
		
		query = "SELECT COUNT(term) totalcount, month FROM " + TableName.TIMER_LIST + 
				" WHERE year = " + year + 
				" GROUP BY month" +
				" ORDER BY month DESC;";
		
		Cursor c = db.select(context, query);
		
		int monthIdx = c.getColumnIndex("month");
		int totalcountIdx = c.getColumnIndex("totalcount");
		
		c.moveToFirst();
		
		for (int i = 0; i < c.getCount(); i++)
		{
			TimerData data = new TimerData();
			data.setMonth(c.getInt(monthIdx));
			data.setCount(c.getString(totalcountIdx));
			
			list.add(data);
			
			c.moveToNext();
		}
		
		if(c != null) c.close();
		
		return list;
	}
	
	public ArrayList<TimerData> getTimerMonthCount(int year, int month)
	{
		ArrayList<TimerData> list = new ArrayList<TimerData>();
		
		String query = "";
		
		query = "SELECT COUNT(term) totalcount, day FROM " + TableName.TIMER_LIST + 
				" WHERE year = " + year + " AND month = " + month +
				" GROUP BY day" +
				" ORDER BY day DESC;";
		
		Cursor c = db.select(context, query);
		
		int dayIdx = c.getColumnIndex("day");
		int totalcountIdx = c.getColumnIndex("totalcount");
		
		c.moveToFirst();
		
		for (int i = 0; i < c.getCount(); i++)
		{
			TimerData data = new TimerData();
			data.setDay(c.getInt(dayIdx));
			data.setCount(c.getString(totalcountIdx));
			
			list.add(data);
			
			c.moveToNext();
		}
		
		if(c != null) c.close();
		
		return list;
	}
	
	public ArrayList<TimerData> getTimerWeekdayCount(
			int year1, int month1, int day1, 
			int year2, int month2, int day2)
	{
		ArrayList<TimerData> list = new ArrayList<TimerData>();
		
		String query = "";
		
		query = "SELECT COUNT(term) totalcount FROM " + TableName.TIMER_LIST + 
				" WHERE year = " + year1 + " AND month = " + month1 + " AND day >= " + day1 +
				" AND year = " + year2 + " AND month = " + month2 + " AND day <= " + day2 +
				" GROUP BY day" +
				" ORDER BY day DESC;";
		
		Cursor c = db.select(context, query);
		
		int totalcountIdx = c.getColumnIndex("totalcount");
		
		c.moveToFirst();
		
		int weekday = 0;
		
		for (int i = 0; i < c.getCount(); i++, weekday++)
		{
			TimerData data = new TimerData();
			data.setDay(weekday);
			data.setCount(c.getString(totalcountIdx));
			
			list.add(data);
			
			c.moveToNext();
		}
		
		if(c != null) c.close();
		
		return list;
	}
	
	public ArrayList<TimerData> getTimerYearTerm(int year)
	{
		ArrayList<TimerData> list = new ArrayList<TimerData>();
		
		String query = "";
		
		query = "SELECT SUM(term) totalterm, month FROM " + TableName.TIMER_LIST + 
				" WHERE year = " + year + 
				" GROUP BY month" +
				" ORDER BY month DESC;";
		
		Cursor c = db.select(context, query);
		
		int monthIdx = c.getColumnIndex("month");
		int totaltermIdx = c.getColumnIndex("totalterm");
		
		c.moveToFirst();
		
		for (int i = 0; i < c.getCount(); i++)
		{
			TimerData data = new TimerData();
			data.setMonth(c.getInt(monthIdx));
			data.setTerm(c.getString(totaltermIdx));
			
			list.add(data);
			
			c.moveToNext();
		}
		
		if(c != null) c.close();
		
		return list;
	}
	
	public ArrayList<TimerData> getTimerMonthTerm(int year, int month)
	{
		ArrayList<TimerData> list = new ArrayList<TimerData>();
		
		String query = "";
		
		query = "SELECT SUM(term) totalterm, day FROM " + TableName.TIMER_LIST + 
				" WHERE year = " + year + " AND month = " + month + 
				" GROUP BY day" +
				" ORDER BY day DESC;";
		
		Cursor c = db.select(context, query);
		
		int dayIdx = c.getColumnIndex("day");
		int totaltermIdx = c.getColumnIndex("totalterm");
		
		c.moveToFirst();
		
		for (int i = 0; i < c.getCount(); i++)
		{
			TimerData data = new TimerData();
			data.setDay(c.getInt(dayIdx));
			data.setTerm(c.getString(totaltermIdx));
			
			list.add(data);
			
			c.moveToNext();
		}
		
		if(c != null) c.close();
		
		return list;
	}
	
	public ArrayList<TimerData> getTimerWeekdayTerm(int year1, int month1, int day1, 
			int year2, int month2, int day2)
	{
		ArrayList<TimerData> list = new ArrayList<TimerData>();
		
		String query = "";
		
		query = "SELECT SUM(term) totalterm FROM " + TableName.TIMER_LIST + 
				" WHERE year = " + year1 + " AND month = " + month1 + " AND day >= " + day1 +
				" AND year = " + year2 + " AND month = " + month2 + " AND day <= " + day2 +
				" GROUP BY day" +
				" ORDER BY day DESC;";
		
		Cursor c = db.select(context, query);
		
		int totaltermIdx = c.getColumnIndex("totalterm");
		
		c.moveToFirst();
		
		int weekday = 0;
		
		for (int i = 0; i < c.getCount(); i++, weekday++)
		{
			TimerData data = new TimerData();
			data.setDay(weekday);
			data.setTerm(c.getString(totaltermIdx));
			
			list.add(data);
			
			c.moveToNext();
		}
		
		if(c != null) c.close();
		
		return list;
	}
}
